SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name='loadTraceFile')
BEGIN
	DROP PROCEDURE trace.loadtracefile
END
GO
/*****
Created		:sfibich
ScriptName	:trace.proc.loadTraceFile.sql
Version		:1.5.120127
Purpose		:This creates a procedure that will a trace file to
			 a table.  This does not stop the trace.
Example		:exec trace.loadTraceFile 
			 @traceName='Steve201201272'
			 ,@logging=1
			 ,@fullScan=1
			 ,@compress=1
			 ,@index=1
*****/
CREATE PROCEDURE trace.loadTraceFile
@traceName VARCHAR(100) = NULL
,@traceFolderPath VARCHAR(100) ='C:\temp\'
,@genericTable BIT =0
,@logging TINYINT=0
,@fullscan BIT =0
,@compress BIT = 0
,@index BIT =1
AS
BEGIN
SET nocount ON

/*logging variables*/
DECLARE @set_id uniqueIdentifier; 
DECLARE @procName NVARCHAR(257);
DECLARE @message VARCHAR(1000);

SELECT @procName=metadata.Twopartname(@@PROCID);
SET @set_id=NEWID()

IF @logging=1
BEGIN
	set @message='Starting to load trace.'+@traceName	
	EXEC metadata.logInsert
	@set_id=@set_id
	,@calling_sql_name =@procName
	,@step=1
	,@total_steps=12
	,@message=@message
END

DECLARE @traceFileName VARCHAR(100);
DECLARE @sqlString NVARCHAR(1000);
DECLARE @traceNameNonQuote VARCHAR(100);

SET @traceName=REPLACE(Quotename(@traceName,''),'.trc','');
SET @traceFileName=Quotename(@traceFolderPath+REPLACE(REPLACE(@traceName,'[',''),']','')+'.trc','''');
SET @traceNameNonQuote=REPLACE(REPLACE(@traceName,'[',''),']','')

IF @traceName IS NULL
BEGIN
	SELECT '@traceName required; please choose from one of the following:' AS [Error Message];
	/*get existing files in the trace directory*/
	DECLARE @dirTree AS TABLE(
	subdirectory VARCHAR(255)
	,depth INT
	,files INT
	)
	INSERT INTO @dirTree
	EXEC Xp_dirtree @traceFolderPath,1,1
	SELECT subdirectory AS tracefiles
	FROM @dirTree
	WHERE subdirectory NOT LIKE '%[_]%'
	RETURN;
END

IF EXISTS (SELECT * FROM sys.tables WHERE name=@traceNameNonQuote AND Schema_name(schema_id)='trace')
BEGIN
	IF @logging=1
	BEGIN
		set @message='Dropping table trace.'+@traceName	
		EXEC metadata.logInsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=2
		,@total_steps=12
		,@message=@message
	END
	EXEC ('drop table trace.'+@traceName);
END


/*Create and load tables*/
IF @genericTable=0
BEGIN

	SET @sqlString =
	'CREATE TABLE [trace].'+@traceName+'(
		[id] [int] IDENTITY(1,1) NOT NULL,
		[importDate] [datetime] NOT NULL,
		[TextData] nvarchar(max) NULL,
		[NTUserName] [nvarchar](256) NULL,
		[HostName] [nvarchar](256) NULL,
		[ClientProcessID] [int] NULL,
		[ApplicationName] [nvarchar](256) NULL,
		[LoginName] [nvarchar](256) NULL,
		[SPID] [int] NULL,
		[Duration] [bigint] NULL,
		[StartTime] [datetime] NULL,
		[EndTime] [datetime] NULL,
		[Reads] [bigint] NULL,
		[Writes] [bigint] NULL,
		[CPU] [int] NULL,
		[ServerName] [nvarchar](256) NULL,
		[EventClass] [int] NOT NULL,
		[ObjectName] [nvarchar](256) NULL,
		[DatabaseName] [nvarchar](256) NULL,
		[RowCounts] [bigint] NULL,
		TransactionID bigInt null,
		XactSequence bigInt null,
		EventSequence bigInt null,
	 CONSTRAINT pk_'+@traceNameNonQuote+' PRIMARY KEY CLUSTERED ([id] ASC))'

	EXEC (@sqlString)
	SET @sqlString=	'insert into trace.'+@traceName+'
	([importDate],[TextData],[NTUserName],[HostName]
	,[ClientProcessID],[ApplicationName],[LoginName],[SPID],[Duration]
	,[StartTime],[EndTime],[Reads],[Writes],[CPU],[ServerName],[EventClass]
	,[ObjectName],[DatabaseName],[RowCounts]
	,TransactionID,XactSequence,EventSequence) 
	select
	getdate() importDate
	,[TextData]
	,[NTUserName]
	,[HostName]
	,[ClientProcessID]
	,[ApplicationName]
	,[LoginName]
	,[SPID]
	,[Duration]
	,[StartTime]
	,[EndTime]
	,[Reads]
	,[Writes]
	,[CPU]
	,[ServerName]
	,[EventClass]
	,[ObjectName]
	,[DatabaseName]
	,[RowCounts]
	,TransactionID
	,XactSequence
	,EventSequence
	from
	fn_trace_getTable('+@traceFileName+',default)'

	EXEC (@sqlString)

	EXEC metadata.Loginsert
	@set_id=@set_id
	,@calling_sql_name =@procName
	,@step=3
	,@total_steps=12
	,@message='Standard trace table load'
	
END
ELSE
BEGIN
	SET @sqlString=	'select
	identity(int,1,1) as id
	,getdate() importDate
	,*
	into
	trace.'+@traceName+'
	from
	fn_trace_getTable('+@traceFileName+',default)'

	EXEC metadata.Loginsert
	@set_id=@set_id
	,@calling_sql_name =@procName
	,@step=4
	,@total_steps=12
	,@message='Generic trace table load'

	EXEC (@sqlString)

	SET @sqlString='alter table trace.'+@traceName+'add constraint pk_'+@traceNameNonQuote+' primary key (id)'

	EXEC metadata.Loginsert
	@set_id=@set_id
	,@calling_sql_name =@procName
	,@step=5
	,@total_steps=12
	,@message='Standard trace PK Created'

	EXEC (@sqlString)

END

/*Add indexes*/
IF @index=1
BEGIN
	SET @sqlString='create index idx_'+@traceNameNonQuote+'_eventClass on trace.'+@traceName+'(eventClass) '
	EXEC (@sqlString)
	IF @logging=1
	BEGIN
		EXEC metadata.Loginsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=6
		,@total_steps=12
		,@message='Index on eventClass created'
	END

	SET @sqlString='create index idx_'+@traceNameNonQuote+'_startTime on trace.'+@traceName+'(startTime) '
	EXEC (@sqlString)
	IF @logging=1
	BEGIN
		EXEC metadata.Loginsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=7
		,@total_steps=12
		,@message='Index on startTime created'
	END

	SET @sqlString='create index idx_'+@traceNameNonQuote+'_endTime on trace.'+@traceName+'(endTime) '
	EXEC (@sqlString)
	IF @logging=1
	BEGIN
		EXEC metadata.Loginsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=8
		,@total_steps=12
		,@message='Index on endTime created'
	END

	SET @sqlString='create index idx_'+@traceNameNonQuote+'_loginName on trace.'+@traceName+'(loginName) '
	EXEC (@sqlString)
	IF @logging=1
	BEGIN
		EXEC metadata.Loginsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=9
		,@total_steps=12
		,@message='Index on loginName created'
	END
END

/*compress the table*/
IF @compress=1
BEGIN
	SET @sqlString='ALTER TABLE trace.'+@traceName+' REBUILD WITH (DATA_COMPRESSION = PAGE) '
	EXEC (@sqlString)
	IF @logging=1
	BEGIN
		EXEC metadata.Loginsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=10
		,@total_steps=12
		,@message='Compression table complete'
	END
END

/*fullscan the table*/
IF @fullscan=1
BEGIN
	SET @sqlString='UPDATE STATISTICS trace.'+@traceName+' WITH FULLSCAN, NORECOMPUTE '
	IF @logging=1
	BEGIN
		EXEC metadata.Loginsert
		@set_id=@set_id
		,@calling_sql_name =@procName
		,@step=11
		,@total_steps=12
		,@message='FullScan Complete'
	END
	EXEC (@sqlString)
END

IF @logging=1
BEGIN
	EXEC metadata.Loginsert
	@set_id=@set_id
	,@calling_sql_name =@procName
	,@step=12
	,@total_steps=12
	,@message='Complete'
END

RETURN;
END
GO

if exists (select 1 from sys.objects where name='loadTraceFile' and SCHEMA_NAME(schema_id)='trace')
BEGIN
	merge metadata.utilityInfo as t
	using (
	select 
	'trace.loadTraceFile' as info_desc
	,'1.5.120127' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
END
